package com.hylanda.pb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.hylanda.pb.entity.DisplayConfig;
import com.hylanda.pb.model.DisplayConfigQo;

/**
 * 动态显示配置DAO
 * 
 * @author linda
 * 
 */
@Repository
public class DisplayConfigDao extends SqlServerBaseDao {
	public JdbcTemplate getTemplate() {
		return super.template;
	}

	/**
	 * 加载列表
	 * 
	 * @param qo
	 * @return
	 */
	public Page<DisplayConfig> findPage(DisplayConfigQo qo) {
		Pageable pageable = new PageRequest(qo.getPage(), qo.getSize(), Sort.Direction.DESC, "id");
		int minRowNumber = qo.getPage() * qo.getSize();
		StringBuilder buf = new StringBuilder("select top ");
		buf.append(qo.getSize())
				.append(" id,type_no,type_tag,processor_en_name,input_time FROM(SELECT ROW_NUMBER () OVER (ORDER BY id DESC) RowNumber ,* FROM sys_readfromdb_config");
		// 构造查询条件
		StringBuilder cond = new StringBuilder(" where isValid=1");
		if (!StringUtils.isEmpty(qo.getTypeTag())) {
			cond.append(" and type_tag like'%").append(qo.getTypeTag()).append("%'");
		}
		if (!StringUtils.isEmpty(qo.getEnProcessorName())) {
			cond.append(" and processor_en_name like'%").append(qo.getEnProcessorName()).append("%'");
		}
		buf.append(cond);
		buf.append(" ) A WHERE A.RowNumber >").append(minRowNumber);
		List<Map<String, Object>> list = super.template.queryForList(buf.toString());
		List<DisplayConfig> results = new ArrayList<DisplayConfig>();
		for (Map<String, Object> map : list) {
			DisplayConfig dyAttr = new DisplayConfig();
			dyAttr.setId((Long) map.get("id"));
			dyAttr.setTypeNo((String) map.get("type_no"));
			dyAttr.setTypeTag((String) map.get("type_tag"));
			dyAttr.setEnProcessorName((String) map.get("processor_en_name"));
			dyAttr.setDate((Timestamp) map.get("input_time"));
			results.add(dyAttr);
		}
		int total = queryCount("select count(*) from sys_readfromdb_config".concat(cond.toString()));
		return new PageImpl<DisplayConfig>(results, pageable, total);
	}

	public int delete(Long id) {
		String sql = "update sys_readfromdb_config set isValid=0 where id=?";
		return super.template.update(sql, new Object[] { id });
	}

	/**
	 * 新增保存
	 * 
	 * @param entity
	 * @return
	 */
	public DisplayConfig save(final DisplayConfig entity) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		super.template.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				int i = 0;
				String sql = "insert into sys_readfromdb_config(type_no,type_tag,processor_en_name,display_config) values (?,?,?,?)";
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(++i, entity.getTypeNo());
				ps.setString(++i, entity.getTypeTag());
				ps.setString(++i, entity.getEnProcessorName());
				ps.setString(++i, entity.getDisplayConfig());
				return ps;
			}
		}, keyHolder);
		long pid = keyHolder.getKey().longValue();
		entity.setId(pid);
		return entity;
	}

	/**
	 * 根据id查询其他属性
	 * 
	 * @param id
	 * @return
	 */
	public DisplayConfig findOne(Long id) {
		String sql = "select type_no,type_tag,processor_en_name,display_config from sys_readfromdb_config where id=?";
		Map<String, Object> map = super.template.queryForMap(sql, new Object[] { id });
		DisplayConfig displayConfig = new DisplayConfig();
		displayConfig.setId(id);
		displayConfig.setTypeNo((String) map.get("type_no"));
		displayConfig.setTypeTag((String) map.get("type_tag"));
		displayConfig.setEnProcessorName((String) map.get("processor_en_name"));
		displayConfig.setDisplayConfig((String) map.get("display_config"));
		return displayConfig;
	}

	/**
	 * 根据id更新其他属性
	 * 
	 * @param displayConfig
	 * @return
	 */
	public int update(final DisplayConfig displayConfig) {
		String sql = "update sys_readfromdb_config set type_no=?,type_tag=?,processor_en_name=?,display_config=? where id=?";
		return super.template.update(sql, new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, displayConfig.getTypeNo());
				ps.setString(2, displayConfig.getTypeTag());
				ps.setString(3, displayConfig.getEnProcessorName());
				ps.setString(4, displayConfig.getDisplayConfig());
				ps.setLong(5, displayConfig.getId());
			}
		});
	}
}
